package pers.zw.study.mysql;

import org.junit.Test;
import pers.zw.study.mysql.utils.JdbcUtils;

import java.math.BigDecimal;
import java.sql.*;

public class JdbcBatchHandleByPrepareStatement {

    /**
     * 模拟转账成功
     */
    @Test
    public void testJdbcBatchHandleByPrepareStatement() {
        long startTime = System.currentTimeMillis();
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JdbcUtils.getConnection();
            String sql = "INSERT INTO test.testbatch VALUES(?, ?, ?)";
            preparedStatement = connection.prepareStatement(sql);

            for (int i = 1; i < 100000; i++) {
                preparedStatement.setInt(1, i);
                preparedStatement.setString(2, "7-" + i);
                preparedStatement.setInt(3, i * 10);
                preparedStatement.addBatch();
                if (i % 1000 == 0) {
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                }
            }
            preparedStatement.executeBatch();
            preparedStatement.clearBatch();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, null);
        }

        long endTime = System.currentTimeMillis();
        System.out.println("Cost: " + (endTime - startTime) / 1000 + " seconds!");
    }

    /**
     * 模拟转账出现异常，自动回滚事务
     */
    @Test
    public void testTransaction2() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            connection.setAutoCommit(false);
            String sql1 = "UPDATE account SET money = money - 100 WHERE name = 'A'";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();
            //模仿出现异常
            int x = 1/0;
            String sql2 = "UPDATE account SET money = money + 100 WHERE name = 'B'";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            connection.commit();
            System.out.println("Congratulations!");
        } catch (Exception e) {
            try {
                assert connection != null;
                connection.rollback();
                System.out.println("Rollback successfully!");
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            JdbcUtils.release(connection, preparedStatement, resultSet);
        }
    }

    /**
     * 设置事务回滚点
     * 只是回滚sql，对JDBC没作用
     */
    @Test
    public void testTransaction3() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Savepoint savepoint = null;

        try {
            connection = JdbcUtils.getConnection();
            connection.setAutoCommit(false);

            String sql1 = "UPDATE account SET money = money - 100 WHERE name = 'A'";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();
            System.out.println("sql1 created successfully!");
            //设置事务回滚点
            savepoint = connection.setSavepoint();

            String sql2 = "UPDATE account SET money = money + 100 WHERE name = 'B'";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            System.out.println("sql2 created successfully!");
            //此处出现异常，后面sql3执行中断
            int x = 1/0;
            String sql3 = "UPDATE account SET money = money + 100 WHERE name = 'C'";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            System.out.println("sql3 created successfully!");
        } catch (Exception e) {
            try {
                assert connection != null;
                connection.rollback(savepoint);
                connection.commit();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            JdbcUtils.release(connection, preparedStatement, resultSet);
        }
    }
}
